﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using GestorSQL.BLL;
using System.Threading;
using GestorSQL.GUI.Properties;
using GestorSQL.BusinessEntities;
using GestorSQL.BLL.QueryExecutions;
using RichTextFormatter.ColorRichTextBox;

namespace GestorSQL.GUI
{
    public partial class QueryWindow : Form
    {
        private CaseManager _caseManager = new CaseManager();
        private clsColorRichTextBox m_ColorRTB;

        #region Propiedades
        public bool CanUndo { get { return rtbQuery.CanUndo; } }

        public bool CanRedo { get { return rtbQuery.CanRedo; } }

        public bool IsTextSelected { get { return !string.IsNullOrEmpty(rtbQuery.SelectedText); } }

        public string CaseNumber { get { return _caseManager.CaseNumber; } }

        public CaseManager CaseManager { get { return _caseManager; } }

        public bool IsConnectionSetted { get { return _caseManager.HasConnection; } }

        public Conexion Connection { get { return _caseManager.Connection; } }
        #endregion

        #region Constructores
        /// <summary>
        /// Crea la ventana y crea el caso con los datos ingresados
        /// </summary>
        /// <param name="caseNumber">Numero del caso</param>
        /// <param name="ownerUserId">Usuario Dueno</param>
        public QueryWindow(string caseNumber, string ownerUserId)
        {
            InitializeComponent();

            this.Text = Resources.Query_WindowText + " - " + caseNumber;
            _caseManager.StartCase(caseNumber, Thread.CurrentPrincipal.Identity.Name, ownerUserId);
            _FormatText();
        }

        /// <summary>
        /// Crea la ventana y abre el caso deado el numero del caso.
        /// </summary>
        /// <param name="caseNumber">Numero del caso</param>
        public QueryWindow(string caseNumber)
        {
            InitializeComponent();

            this.Text = Resources.Query_WindowText + " - " + caseNumber;
            OpenCase(caseNumber);
            _FormatText();
        }
        #endregion

        #region Metodos Publicos
        public void SaveCase()
        {
            try
            {
                _caseManager.Statement = _GetQueryText();
                _caseManager.SaveCase(true);
            }
            catch (NullReferenceException)
            {
                MessageBox.Show(Resources.Case_NotCaseLoaded, Resources.Case_SaveCaseText, MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
            }
        }

        public void SetConnection(Conexion conn)
        {
            _caseManager.SetConnection(conn, Ambiente.Desarrollo);

        }

        public void SetProductionConnection(Conexion conn)
        {
            _caseManager.SetConnection(conn, Ambiente.Produccion);
           

        }

        public void ProcessKbdCtrlShortcuts(KeyEventArgs e)
        {
            RichTextBox t = rtbQuery;
            if (e.KeyData == (Keys.C | Keys.Control))
            {
                t.Copy();
                e.Handled = true;
            }
            else if (e.KeyData == (Keys.X | Keys.Control))
            {
                t.Cut();
                e.Handled = true;
            }
            else if (e.KeyData == (Keys.V | Keys.Control))
            {
                t.Paste();
                e.Handled = true;
            }
            else if (e.KeyData == (Keys.E | Keys.Control))
            {
                t.SelectAll();
                e.Handled = true;
            }
            else if (e.KeyData == (Keys.Z | Keys.Control))
            {
                t.Undo();
                e.Handled = true;
            }
            else if (e.KeyData == (Keys.Y | Keys.Control))
            {
                t.Redo();
                e.Handled = true;
            }
        }

        public void Execute(bool Selected, ResultTarget resultTarget)
        {

            string comentarioEjecucion = string.Empty;

            if (_caseManager.CurrentCase.IsProduction(_caseManager.CurrentCase.Historia.Estado_))
            {
                TextWindow textWindowComentario = new TextWindow("Descripción Ejecución", "", Properties.Resources.Description + ":", false, false, true);
                if (textWindowComentario.ShowDialog().Equals(DialogResult.OK))
                    comentarioEjecucion = textWindowComentario.Texto;

            }



            ResetResultPanel();
            StringBuilder sb = new StringBuilder();
            rtbMessage.Text = string.Empty;

            string query = Selected ? rtbQuery.SelectedText : rtbQuery.Text;

            try
            {
                using (BLLCommand bllCommand = _caseManager.CreateBLLCommand())
                {
                    bllCommand.ComentarioEjecucion = comentarioEjecucion;

                    BLLReader reader = bllCommand.ExecuteQuery(query);
                    int results = 0;
                    do
                    {
                        if (reader.ResultType == ResulType.Fail || reader.ResultType == ResulType.Modification)
                        {
                            sb.AppendLine(reader.Message).AppendLine();
                            rtbMessage.Text = sb.ToString();
                            tabMenu.SelectedTab = tabMessage;
                        }
                        else if (reader.ResultType == ResulType.Select)
                        {
                            if (resultTarget == ResultTarget.Grid)
                            {
                                DataGridView grid = CreateGrid(reader, ref results);
                                while (reader.Read())
                                {
                                    object[] row = reader.GetRowData();
                                    grid.Rows.Add(row);
                                }
                            }
                            else if (resultTarget == ResultTarget.Text)
                            {
                                string separationCharacter = "\t";
                                string separationCharacterEncode = string.Format("<TAB>&{0};</TAB>", Guid.NewGuid().ToString());
                                bool replaceSeparationCharacter = true;
                                string path = string.Empty;
                                string fileName = string.Empty;
                                int[] colsLength = new int[reader.FieldCount];

                                BLLTextWriter writer = BLLTextWriter.GetInstanceWithTempFileName(false);
                                path = writer.FilePath;
                                fileName = writer.FileNameWithOutExtesion;

                                writer.WriteLine(GetTextHeaders(reader, separationCharacter, separationCharacterEncode, replaceSeparationCharacter, ref colsLength));
                                while (reader.Read())
                                    writer.WriteLine(_GetReaderString(reader.GetRowData(), separationCharacter, separationCharacterEncode, replaceSeparationCharacter, ref colsLength));

                                writer.Close();

                                RichTextBox textBox = CreateTextBox(reader, ref results);
                                BLLTextReader tReader = new BLLTextReader(path, fileName);

                                string linea = tReader.ReadLine();
                                while (linea != null)
                                {
                                    string[] cells = linea.Split(new string[] { separationCharacter }, StringSplitOptions.None);
                                    for (int i = 0; i < cells.Length - 1; i++)
                                    {
                                        string cell = cells[i];
                                        string text = replaceSeparationCharacter ? cell.Replace(separationCharacterEncode, separationCharacter) : cell;
                                        textBox.AppendText(text.PadRight(colsLength[i] + 1, ' '));
                                    }
                                    textBox.AppendText(Environment.NewLine);
                                    linea = tReader.ReadLine();
                                }

                            }
                            sb.AppendLine(reader.Message).AppendLine();
                            rtbMessage.Text = sb.ToString();
                        }
                    } while (reader.NextResult());
                }
            }
            catch (Exception ex)
            {
                sb.AppendLine(ex.Message).AppendLine();
                rtbMessage.Text = sb.ToString();
                tabMenu.SelectedTab = tabMessage;
            }

        }

        #endregion

        #region Handlers
        private void QueryWindow_FormClosing(object sender, FormClosingEventArgs e)
        {
            try
            {
                if (_caseManager.CasoParaEjecutarEnProduccion != Resultado.Exitoso)
                {
                    _caseManager.Statement = _GetQueryText();
                    if (!_caseManager.IsDirty)
                        _caseManager.SaveCase(true);
                    else
                    {
                        if (MessageBox.Show(Resources.Case_WantToSave, Resources.Case_SaveCaseText, MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
                            _caseManager.SaveCase(true);
                        else
                            _caseManager.SaveCase(false);
                    }
                }
            }
            catch (NullReferenceException)
            {
                MessageBox.Show(Resources.Case_NotCaseLoaded, Resources.Case_SaveCaseText, MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
            }
        }
        #endregion

        #region Metodos Privados
        private void _FormatText()
        {
            clsColorRichTextBoxSchema schema = new clsColorRichTextBoxSchema(clsColorRichTextBoxSchema.enColorSchemaType.SQL);
            m_ColorRTB = new RichTextFormatter.ColorRichTextBox.clsColorRichTextBox(rtbQuery, schema);
            m_ColorRTB.RecolorEntireText();
        }

        private void OpenCase(string caseNumber)
        {
            try
            {
                _caseManager.OpenCase(caseNumber, Thread.CurrentPrincipal.Identity.Name);

                rtbQuery.Text = _caseManager.Statement;

            }
            catch (NullReferenceException)
            {
                MessageBox.Show(Resources.Case_NotCaseLoaded, Resources.Case_OpenCaseText, MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
            }
        }

        private void ResetResultPanel()
        {
            tlp_Results.Controls.Clear();
            tlp_Results.RowStyles.Clear();
            this.tlp_Results.Size = new System.Drawing.Size(503, 184);
            this.tlp_Results.RowCount = 1;
        }

        private DataGridView CreateGrid(BLLReader reader, ref int results)
        {
            if (results > 0)
            {
                this.tlp_Results.RowCount = results + 1;
                this.tlp_Results.RowStyles.Add(new RowStyle(SizeType.Percent, 100F));
            }
            DataGridView grid = new DataGridView();
            this.tlp_Results.Controls.Add(grid, 0, results);

            grid.Name = string.Format("ResultGrid_{0}", results++);
            grid.AllowUserToAddRows = false;
            grid.AllowUserToDeleteRows = false;
            grid.ColumnHeadersHeightSizeMode = System.Windows.Forms.DataGridViewColumnHeadersHeightSizeMode.AutoSize;
            grid.Dock = DockStyle.Fill;
            grid.BackgroundColor = Color.White;
            int columnCount = 0;
            foreach (string column in reader.Columns)
                grid.Columns.Add(string.Format("Column_{0}", columnCount++), column);
            return grid;
        }

        private RichTextBox CreateTextBox(BLLReader reader, ref int results)
        {
            if (results > 0)
            {
                this.tlp_Results.RowCount = results + 1;
                this.tlp_Results.RowStyles.Add(new RowStyle(SizeType.Percent, 100F));
            }
            RichTextBox rtbox = new RichTextBox();
            this.tlp_Results.Controls.Add(rtbox, 0, results);

            rtbox.Name = string.Format("ResultTextBox_{0}", results++);
            rtbox.Dock = DockStyle.Fill;
            rtbox.BackColor = Color.White;
            rtbox.ScrollBars = System.Windows.Forms.RichTextBoxScrollBars.Both;
            rtbox.WordWrap = false;
            rtbox.Font = new System.Drawing.Font("Courier New", 9.25F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));


            return rtbox;
        }

        private string GetTextHeaders(BLLReader reader, string separationCharacter, string separationCharacterEncode, bool replaceSeparationCharacter, ref int[] colsLength)
        {
            StringBuilder sbHeader = new StringBuilder();
            StringBuilder sbHeader2 = new StringBuilder();
            for (int i = 0; i < reader.Columns.Count; i++)
            {
                string column = reader.Columns[i];
                colsLength[i] = colsLength[i] < column.Length ? column.Length : colsLength[i];

                string columnText = replaceSeparationCharacter ? column.Replace(separationCharacter, separationCharacterEncode) : column;
                sbHeader.AppendFormat("{0}{1}", columnText, separationCharacter);
                sbHeader2.AppendFormat("{0}{1}", "-".PadRight(column.Length, '-'), separationCharacter);
            }
            sbHeader.AppendLine();
            sbHeader.Append(sbHeader2.ToString());
            return sbHeader.ToString();
        }

        private string _GetReaderString(object[] ret, string separationCharacter, string separationCharacterEncode, bool replaceSeparationCharacter, ref int[] colsLength)
        {
            StringBuilder sbResult = new StringBuilder();
            for (int i = 0; i < ret.Length; i++)
            {
                colsLength[i] = colsLength[i] < ret[i].ToString().Length ? ret[i].ToString().Length : colsLength[i];

                string cellText = replaceSeparationCharacter ? ret[i].ToString().Replace(separationCharacter, separationCharacterEncode) : ret[i].ToString();

                sbResult.AppendFormat("{0}{1}", cellText, separationCharacter);

            }
            return sbResult.ToString();
        }

        private string _GetQueryText()
        {
            StringBuilder sb = new StringBuilder();
            foreach (string linea in rtbQuery.Lines)
                sb.AppendLine(linea);
            return sb.ToString().Trim();
        }
        #endregion

        #region Metodos privados
        //string header = @"{\rtf1\ansi\ansicpg1252\deff0\deflang3082{\fonttbl{\f0\fnil\fprq1\fcharset0 Courier New;}{\f1\fswiss\fcharset0 Arial;}}{\colortbl ;\red0\green0\blue255 ;\red128\green128\blue128 ;\red255\green0\blue255 ;\red255\green0\blue0 ;}{\*\generator Msftedit 5.41.21.2500;}";
        //string footer = @"\f1\par}";
        //string[] reservedWords = new string[] { "SELECT", "UPDATE" };
        //private string currentText = string.Empty;
        //private string _FormatString()
        //{
        //    StringBuilder sb = new StringBuilder();
        //    sb.Append(header);
        //    string[] words = rtbQuery.Text.Replace(((char)10).ToString(),@"\par").Split(new char[] { ' ' });
        //    int totalwords = words.Length;
        //    int wordCount = 0;
        //    string header2 = @"\viewkind4\uc1\pard{0}\f0\fs20";
        //    string lastStyle = "";

        //    if (totalwords == 1 && words[0] == "")
        //    {
        //        sb.AppendFormat(header2, @"\cf0 ");
        //        sb.Append(" ");
        //        lastStyle == "cf0";
        //    }else if(totalwords==1 &&


        //    bool endWithSpace = rtbQuery.Text.EndsWith(" ");
        //    string wordAux = string.Empty;

        //    foreach (string word in words)
        //    {
        //        wordAux = word; 
        //        if (string.IsNullOrEmpty(word))
        //            wordAux = " ";
        //        string[] swords = wordAux.Split(new char[] { (char)10 });
        //        int totalswords = swords.Length;
        //        int swordCount = 0;
        //        bool endWithEnter = wordAux.EndsWith(((char)10).ToString());
        //        foreach (string sword in swords)
        //        {
        //            if (string.IsNullOrEmpty(sword))
        //            { }
        //            else if (sword == " ")
        //            { }//    sb.Append(" ");
        //            else if (_IsReservedWord(sword))
        //                sb.AppendFormat(@"\cf1 {0} ", sword);
        //            else
        //                sb.AppendFormat(@"\cf0 {0}", sword);

        //            swordCount++;
        //            if ((totalswords > 1 && swordCount < totalswords) || (swordCount == totalswords && endWithEnter))
        //                sb.AppendFormat(@"\par");
        //        }
        //        wordCount++;
        //        if ((totalwords > 1 && wordCount < totalwords) || (wordCount==totalwords && endWithSpace))
        //            sb.Append(" ");
        //    }
        //    sb.Append(footer);

        //    return sb.ToString();
        //    //return @"\viewkind4\uc1\pard\cf1\f0\fs20 update\cf0  conexion \cf1 set\cf0  Nombre \cf2 =\cf0  \cf2 null\par\par\cf1 SELECT\cf0  \cf2 (\cf1 CASE\par WHEN\cf0  \cf3 OBJECTPROPERTY\cf2 (\cf0 CONSTID\cf2 ,\cf0  \cf4 'CNSTISDISABLED'\cf2 )\cf0  \cf2 =\cf0  0 \cf1 THEN\cf0  \cf4 'ENABLED'\par\cf1 ELSE\cf0  \cf4 'DISABLED'\par\cf1 END\cf2 )\cf0  \cf1 AS\cf0  STATUS\cf2 ,\par\cf3 OBJECT_NAME\cf2 (\cf0 CONSTID\cf2 )\cf0  \cf1 AS\cf0  CONSTRAINT_NAME\cf2 ,\par\cf3 OBJECT_NAME\cf2 (\cf0 FKEYID\cf2 )\cf0  \cf1 AS\cf0  TABLE_NAME\cf2 ,\par\cf3 COL_NAME\cf2 (\cf0 FKEYID\cf2 ,\cf0  FKEY\cf2 )\cf0  \cf1 AS\cf0  COLUMN_NAME\cf2 ,\par\cf3 OBJECT_NAME\cf2 (\cf0 RKEYID\cf2 )\cf0  \cf1 AS\cf0  REFERENCED_TABLE_NAME\cf2 ,\par\cf3 COL_NAME\cf2 (\cf0 RKEYID\cf2 ,\cf0  RKEY\cf2 )\cf0  \cf1 AS\cf0  REFERENCED_COLUMN_NAME\par\cf1 FROM\cf0  SYSFOREIGNKEYS\par\cf1 ORDER\cf0  \cf1 BY\cf0  TABLE_NAME\cf2 ,\cf0  CONSTRAINT_NAME\cf2 ,\cf0 REFERENCED_TABLE_NAME\cf2 ,\cf0  KEYNO\par\f1\par}";
        //}
        #endregion

    }

    #region Enumeracion Tipos Resultado
    public enum ResultTarget
    {
        None = 0,
        Grid,
        Text,
        File
    }
    #endregion
}

